package cn.stylefeng.guns.modular.tool.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.io.Serializable;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import cn.stylefeng.guns.base.pojo.page.LayuiPageFactory;
import cn.stylefeng.guns.base.pojo.page.LayuiPageInfo;
import cn.stylefeng.guns.core.util.ExcelWrite;
import cn.stylefeng.guns.modular.tool.entity.ToolExpressReconciliationYz;
import cn.stylefeng.guns.modular.tool.mapper.ToolExpressReconciliationYzMapper;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationRfParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYfInstallParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYzInstallParam;
import cn.stylefeng.guns.modular.tool.model.params.ToolExpressReconciliationYzParam;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationYfInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationYzInstallResult;
import cn.stylefeng.guns.modular.tool.model.result.ToolExpressReconciliationYzResult;
import cn.stylefeng.guns.modular.tool.model.result.orderCount;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationRfService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationYfInstallService;
import cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationYzInstallService;
import  cn.stylefeng.guns.modular.tool.service.ToolExpressReconciliationYzService;
import cn.stylefeng.guns.sys.modular.system.entity.User;
import cn.stylefeng.guns.sys.modular.system.service.UserService;
import cn.stylefeng.roses.core.reqres.response.ResponseData;
import cn.stylefeng.roses.core.util.ToolUtil;

/**
 * <p>
 * 邮政对账管理 服务实现类
 * </p>
 *
 * @author zhucx
 * @since 2022-12-14
 */
@Service
public class ToolExpressReconciliationYzServiceImpl extends ServiceImpl<ToolExpressReconciliationYzMapper, ToolExpressReconciliationYz> implements ToolExpressReconciliationYzService {

    @Override
    public void add(ToolExpressReconciliationYzParam param){
        ToolExpressReconciliationYz entity = getEntity(param);
        this.save(entity);
    }

    @Override
    public void delete(ToolExpressReconciliationYzParam param){
        this.removeById(getKey(param));
    }

    @Override
    public void update(ToolExpressReconciliationYzParam param){
        ToolExpressReconciliationYz oldEntity = getOldEntity(param);
        ToolExpressReconciliationYz newEntity = getEntity(param);
        ToolUtil.copyProperties(newEntity, oldEntity);
        this.updateById(newEntity);
    }

    @Override
    public ToolExpressReconciliationYzResult findBySpec(ToolExpressReconciliationYzParam param){
        return null;
    }

    @Override
    public List<ToolExpressReconciliationYzResult> findListBySpec(ToolExpressReconciliationYzParam param){
        return this.baseMapper.findListBySpec(param);
    }

    @Override
    public LayuiPageInfo findPageBySpec(ToolExpressReconciliationYzParam param,String condition,Long operateId){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageList(pageContext, param,condition, operateId);
        return LayuiPageFactory.createPageInfo(page);
    }

    @Override
    public LayuiPageInfo findPageBySpecYzDz(ToolExpressReconciliationYzParam param,String condition){
        Page pageContext = getPageContext();
        IPage page = this.baseMapper.customPageListYzDz(pageContext, param,condition);
        return LayuiPageFactory.createPageInfo(page);
    }

    private Serializable getKey(ToolExpressReconciliationYzParam param){
        return param.getId();
    }

    private Page getPageContext() {
        return LayuiPageFactory.defaultPage();
    }

    private ToolExpressReconciliationYz getOldEntity(ToolExpressReconciliationYzParam param) {
        return this.getById(getKey(param));
    }

    private ToolExpressReconciliationYz getEntity(ToolExpressReconciliationYzParam param) {
        ToolExpressReconciliationYz entity = new ToolExpressReconciliationYz();
        ToolUtil.copyProperties(param, entity);
        return entity;
    }
    
    @Resource
    private UserService userService;

    @Resource
    private ToolExpressReconciliationYzInstallService toolExpressReconciliationYzInstallService;

    @Resource
    private ToolExpressReconciliationRfService toolExpressReconciliationRfService;

    @Resource
    private ToolExpressReconciliationYfInstallService toolExpressReconciliationYfInstallService;

	@Override
	public ResponseData getExceldata(MultipartFile file,Long reconciliationId, ResponseData data) throws IOException{
		String fileName = file.getOriginalFilename();
		
		User user = userService.getcurrentUser();

    	BigDecimal yiq = new BigDecimal("1000");
    	BigDecimal as = new BigDecimal("0");
    	BigDecimal qj03 = new BigDecimal("0.3").setScale(2, BigDecimal.ROUND_HALF_UP);
    	BigDecimal qj05 = new BigDecimal("0.5");
    	BigDecimal yi = new BigDecimal("1");
    	BigDecimal er = new BigDecimal("2");
    	BigDecimal san = new BigDecimal("3");
    	BigDecimal si = new BigDecimal("4");
    	BigDecimal wu = new BigDecimal("5");
    	BigDecimal wudwu = new BigDecimal("5.5");
		
		List<ToolExpressReconciliationYzParam> yzlist = new ArrayList<ToolExpressReconciliationYzParam>();
		List<ToolExpressReconciliationRfParam> rflist = new ArrayList<ToolExpressReconciliationRfParam>();
        
        ToolExpressReconciliationYzInstallParam expressReconciliationYzInstallParam = new ToolExpressReconciliationYzInstallParam();
        expressReconciliationYzInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationYzInstallResult> yzi = toolExpressReconciliationYzInstallService.findListBySpec(expressReconciliationYzInstallParam,user.getOperateId());//邮政报价
        
        ToolExpressReconciliationYfInstallParam expressReconciliationYfInstallParam = new ToolExpressReconciliationYfInstallParam();
        expressReconciliationYfInstallParam.setDeptId(user.getDeptId());
        List<ToolExpressReconciliationYfInstallResult> rfi = toolExpressReconciliationYfInstallService.findListBySpec(expressReconciliationYfInstallParam,user.getOperateId());//软风报价
		
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			data.setMessage("上传文件格式不正确");
			data.setSuccess(false);
			return data;
        }

		boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        InputStream is = file.getInputStream();
        Workbook wb = null;
        if (isExcel2003) {
            wb = new HSSFWorkbook(is);
        } else {
            wb = new XSSFWorkbook(is);
        }
        Sheet sheet = wb.getSheetAt(0);
        
        for (int r = 1; r <= sheet.getLastRowNum()+1; r++) {
        	Row row = sheet.getRow(r);
            if (row == null){
                continue;
            }
            
            ToolExpressReconciliationYzParam expressReconciliationYzParam = new ToolExpressReconciliationYzParam();//邮政账单
            expressReconciliationYzParam.setReconciliationId(reconciliationId);
            
            ToolExpressReconciliationRfParam rfffParam = new ToolExpressReconciliationRfParam();//软风账单
            rfffParam.setV1("yz");
            rfffParam.setReconciliationId(reconciliationId);

            String  customerName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(8));//客户名称
            if (ToolUtil.isNotEmpty(customerName)) {
            	expressReconciliationYzParam.setCustomerName(customerName);
			}

            String  noNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(0));//序号
            if (ToolUtil.isNotEmpty(noNumber)) {
            	expressReconciliationYzParam.setNoNumber(noNumber);
			}
            
            String  receivingTime = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(1));//收寄时间
            if (ToolUtil.isNotEmpty(receivingTime)) {
            	expressReconciliationYzParam.setReceivingTime(receivingTime);
			}
            
            String  product = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(2));//产品
            if (ToolUtil.isNotEmpty(product)) {
            	expressReconciliationYzParam.setProduct(product);
			}
            
            String  orderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(3));//邮件号
            if (ToolUtil.isNotEmpty(orderNumber)) {
            	expressReconciliationYzParam.setOrderNumber(orderNumber);
        		rfffParam.setOrderNumber(orderNumber);//快递单号
			} else {
				break;
			}
            
            String  province = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(4));//寄达省名称
            if (ToolUtil.isNotEmpty(province)) {
            	expressReconciliationYzParam.setProvince(province.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
            	expressReconciliationYzParam.setProvinceName(province);//寄达省名称
            	
            	rfffParam.setProvince(province.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//省份
			}
            
            String  weightK = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(5));//重量(克)
            if (ToolUtil.isNotEmpty(weightK)) {
            	expressReconciliationYzParam.setWeightK(weightK);
            	
            	BigDecimal weightKbig = new BigDecimal(weightK);
            	BigDecimal weightKbigdivide = weightKbig.divide(yiq,2,BigDecimal.ROUND_HALF_UP);
            	expressReconciliationYzParam.setChargingWeightKg(weightKbigdivide.toString());//计费重量(千克)
            	
            	expressReconciliationYzParam.setRoundingWeightKg(weightKbigdivide.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量（向上取整）

            	if(weightKbigdivide.compareTo(yi) == -1) {
            		expressReconciliationYzParam.setWeightRange("1KG以下");
            	}
            	if(weightKbigdivide.compareTo(yi) == 0 || weightKbigdivide.compareTo(yi) == 1) {
                	if(weightKbigdivide.compareTo(er) == -1) {
                		expressReconciliationYzParam.setWeightRange("1-2KG");
                	}
            	}
            	if(weightKbigdivide.compareTo(er) == 0 || weightKbigdivide.compareTo(er) == 1) {
                	if(weightKbigdivide.compareTo(san) == -1 || weightKbigdivide.compareTo(san) == 0) {
                		expressReconciliationYzParam.setWeightRange("2-3KG");
                	}
            	}
            	if(weightKbigdivide.compareTo(san) == 1) {
            		expressReconciliationYzParam.setWeightRange("3KG以上");
            	}
            	
            	rfffParam.setChargingWeightKg(weightKbigdivide.toString());//计费重量
            	rfffParam.setRoundingWeightKg(weightKbigdivide.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
			}
            
            String  postage = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(6));//总邮资
            if (ToolUtil.isNotEmpty(postage)) {
            	BigDecimal postagebig = new BigDecimal(postage).setScale(2, BigDecimal.ROUND_HALF_UP);
            	expressReconciliationYzParam.setPostage(postagebig.toString());

            	if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getChargingWeightKg())) {
            		BigDecimal chargingWeightKgbig = new BigDecimal(expressReconciliationYzParam.getChargingWeightKg());//计费重量(千克)
            		//匹配邮政报价   计算核对邮费  计算邮费差异
            		if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getProvince())) {
            			for (ToolExpressReconciliationYzInstallResult i : yzi) {
            				if(expressReconciliationYzParam.getProvince().equals(i.getProvince())) {
            					if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getProduct())) {
            						if(expressReconciliationYzParam.getProduct().equals("4号标准箱")) {//4号标准箱
            							if(ToolUtil.isNotEmpty(i.getX4())) {
            								expressReconciliationYzParam.setPostageCheck(i.getX4());
            								BigDecimal x4 = new BigDecimal(i.getX4());
            								BigDecimal postageDifference = postagebig.subtract(x4).setScale(2, BigDecimal.ROUND_HALF_UP);
            								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            							}
            							break;
            						}
            						if(expressReconciliationYzParam.getProduct().equals("3号标准箱")) {//3号标准箱
            							if(ToolUtil.isNotEmpty(i.getX3())) {
            								expressReconciliationYzParam.setPostageCheck(i.getX3());
            								BigDecimal x3 = new BigDecimal(i.getX3());
            								BigDecimal postageDifference = postagebig.subtract(x3).setScale(2, BigDecimal.ROUND_HALF_UP);
            								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            							}
            							break;
            						}
            						if(expressReconciliationYzParam.getProduct().equals("2号标准箱")) {//2号标准箱
            							if(ToolUtil.isNotEmpty(i.getX2())) {
            								expressReconciliationYzParam.setPostageCheck(i.getX2());
            								BigDecimal x2 = new BigDecimal(i.getX2());
            								BigDecimal postageDifference = postagebig.subtract(x2).setScale(2, BigDecimal.ROUND_HALF_UP);
            								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            							}
            							break;
            						}
            						if(expressReconciliationYzParam.getProduct().equals("1号标准箱")) {//1号标准箱
            							if(ToolUtil.isNotEmpty(i.getX1())) {
            								expressReconciliationYzParam.setPostageCheck(i.getX1());
            								BigDecimal x1 = new BigDecimal(i.getX1());
            								BigDecimal postageDifference = postagebig.subtract(x1).setScale(2, BigDecimal.ROUND_HALF_UP);
            								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            							}
            							break;
            						}
            					}
            					if(chargingWeightKgbig.compareTo(as) == 1 && chargingWeightKgbig.compareTo(qj03) != 1) {//0 -  300g
            						if(ToolUtil.isNotEmpty(i.getQj03g())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj03g());
        								BigDecimal qj03g = new BigDecimal(i.getQj03g());
        								BigDecimal postageDifference = postagebig.subtract(qj03g).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(qj03) == 1 && chargingWeightKgbig.compareTo(qj05) != 1) {//300g - 500g
            						if(ToolUtil.isNotEmpty(i.getQj35g())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj35g());
        								BigDecimal qj35g = new BigDecimal(i.getQj35g());
        								BigDecimal postageDifference = postagebig.subtract(qj35g).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(expressReconciliationYzParam.getProvince().equals("新疆") || expressReconciliationYzParam.getProvince().equals("西藏") || expressReconciliationYzParam.getProvince().equals("青海")) {
        							if(ToolUtil.isNotEmpty(i.getFirstWeight()) && ToolUtil.isNotEmpty(i.getCWeight())) {//（（首重+（续重*重量））*折扣
        								expressReconciliationYzParam.setFirstWeight(i.getFirstWeight());
        								expressReconciliationYzParam.setWeightContinuation(i.getCWeight());
        								BigDecimal discount = new BigDecimal(1);
        								if(ToolUtil.isNotEmpty(i.getDiscount())) {
        									discount = new BigDecimal(i.getDiscount());
        									expressReconciliationYzParam.setDiscount(i.getDiscount());
        								}
        								BigDecimal firstWeight = new BigDecimal(i.getFirstWeight());//首重
        								BigDecimal cWeight = new BigDecimal(i.getCWeight());//续重
        								BigDecimal xmulz = cWeight.multiply(chargingWeightKgbig).setScale(2, BigDecimal.ROUND_HALF_UP);//续重 *  重量
        								BigDecimal addxmulz = firstWeight.add(xmulz);//首重+（续重*重量）
        								BigDecimal postageCheck = addxmulz.multiply(discount).setScale(2, BigDecimal.ROUND_HALF_UP);//（（首重+（续重*重量））*折扣
        								expressReconciliationYzParam.setPostageCheck(postageCheck.toString());//邮资核对
        								BigDecimal postageDifference = postagebig.subtract(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());//邮资差异
        							}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(qj05) == 1 && chargingWeightKgbig.compareTo(yi) != 1) {//500g - 1kg
            						if(ToolUtil.isNotEmpty(i.getQj51kg())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj51kg());
        								BigDecimal qj51kg = new BigDecimal(i.getQj51kg());
        								BigDecimal postageDifference = postagebig.subtract(qj51kg).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(yi) == 1 && chargingWeightKgbig.compareTo(er) != 1) {//1kg - 2kg
            						if(ToolUtil.isNotEmpty(i.getQj12kg())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj12kg());
        								BigDecimal qj12kg = new BigDecimal(i.getQj12kg());
        								BigDecimal postageDifference = postagebig.subtract(qj12kg).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(er) == 1 && chargingWeightKgbig.compareTo(san) != 1) {//2kg - 3kg
            						if(ToolUtil.isNotEmpty(i.getQj23kg())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj23kg());
        								BigDecimal qj23kg = new BigDecimal(i.getQj23kg());
        								BigDecimal postageDifference = postagebig.subtract(qj23kg).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(san) == 1 && chargingWeightKgbig.compareTo(si) != 1) {//3kg - 4kg
            						if(ToolUtil.isNotEmpty(i.getQj34kg())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj34kg());
        								BigDecimal qj34kg = new BigDecimal(i.getQj23kg());
        								BigDecimal postageDifference = postagebig.subtract(qj34kg).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(si) == 1 && chargingWeightKgbig.compareTo(wu) != 1) {//4kg - 5kg
            						if(ToolUtil.isNotEmpty(i.getQj45kg())) {
        								expressReconciliationYzParam.setPostageCheck(i.getQj45kg());
        								BigDecimal qj45kg = new BigDecimal(i.getQj45kg());
        								BigDecimal postageDifference = postagebig.subtract(qj45kg).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());
            						}
            						break;
            					}
            					if(chargingWeightKgbig.compareTo(wu) == 1) {//5kg以上
        							if(ToolUtil.isNotEmpty(i.getFirstWeight()) && ToolUtil.isNotEmpty(i.getCWeight())) {//（（首重+（续重*重量））*折扣
        								expressReconciliationYzParam.setFirstWeight(i.getFirstWeight());
        								expressReconciliationYzParam.setWeightContinuation(i.getCWeight());
        								BigDecimal discount = new BigDecimal(1);
        								if(ToolUtil.isNotEmpty(i.getDiscount())) {
        									discount = new BigDecimal(i.getDiscount());
        									expressReconciliationYzParam.setDiscount(i.getDiscount());
        								}
        								BigDecimal firstWeight = new BigDecimal(i.getFirstWeight());//首重
        								BigDecimal cWeight = new BigDecimal(i.getCWeight());//续重
        								BigDecimal xmulz = cWeight.multiply(chargingWeightKgbig).setScale(2, BigDecimal.ROUND_HALF_UP);//续重 *  重量
        								BigDecimal addxmulz = firstWeight.add(xmulz);//首重+（续重*重量）
        								BigDecimal postageCheck = addxmulz.multiply(discount).setScale(2, BigDecimal.ROUND_HALF_UP);//（（首重+（续重*重量））*折扣
        								expressReconciliationYzParam.setPostageCheck(postageCheck.toString());//邮资核对
        								BigDecimal postageDifference = postagebig.subtract(postageCheck).setScale(2, BigDecimal.ROUND_HALF_UP);
        								expressReconciliationYzParam.setPostageDifference(postageDifference.toString());//邮资差异
        							}
            						break;
            					}
            				}
            			}
            		}
            	}
			}
            
            String  ycProvince = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(18));//省
            if (ToolUtil.isNotEmpty(ycProvince)) {
    			expressReconciliationYzParam.setYcProvince(ycProvince.replaceAll("省", "").replaceAll("市", "").replaceAll("壮族自治区", "").replaceAll("回族自治区", "").replaceAll("维吾尔自治区", "").replaceAll("自治区", ""));//云仓省份
    			if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getProvince())) {
    				if(expressReconciliationYzParam.getProvince().equals(expressReconciliationYzParam.getYcProvince())) {
    					expressReconciliationYzParam.setProvinceTrue("true");
    				} else {
    					expressReconciliationYzParam.setProvinceTrue("false");
    				}
    			}
			}
            
            String  ycWeightKg = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(7));//云仓重量
            if (ToolUtil.isNotEmpty(ycWeightKg)) {
    			BigDecimal ycWeightKgbig = new BigDecimal(ycWeightKg).setScale(2, BigDecimal.ROUND_HALF_UP);
    			expressReconciliationYzParam.setYcWeightKg(ycWeightKgbig.toString());//云仓重量
    			
    			if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getChargingWeightKg())) {
    				BigDecimal chargingWeightKgbig = new BigDecimal(expressReconciliationYzParam.getChargingWeightKg()).setScale(2,BigDecimal.ROUND_HALF_UP);
        			if(ycWeightKgbig.compareTo(chargingWeightKgbig) == 1) {
        				rfffParam.setChargingWeightKg(ycWeightKgbig.toString());//计费重量
        				rfffParam.setRoundingWeightKg(ycWeightKgbig.setScale( 0, BigDecimal.ROUND_UP ).longValue()+"");//取整重量
        			}
        			
    				BigDecimal subchargingWeightKgbig = chargingWeightKgbig.subtract(ycWeightKgbig).setScale(2,BigDecimal.ROUND_HALF_UP);
    				expressReconciliationYzParam.setWeightDifference(subchargingWeightKgbig.toString());//重量差异
    			}
            }
            
            String  quantity = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(22));//数量
            if (ToolUtil.isNotEmpty(quantity)) {
    			expressReconciliationYzParam.setQuantity(quantity);//数量
        		rfffParam.setNums(quantity);//发货件数
            }
            
            String  outNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(9));//出库单号
            if (ToolUtil.isNotEmpty(outNumber)) {
        		rfffParam.setOutNumber(outNumber);
            }
            
            String  shopNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(10));//店铺编号
            if (ToolUtil.isNotEmpty(shopNumber)) {
        		rfffParam.setShopNumber(shopNumber);
            }
            
            String  shopName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(11));//店铺名称
            if (ToolUtil.isNotEmpty(shopName)) {
        		rfffParam.setShopName(shopName);
            }
            
            String  platform = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(12));//平台站点
            if (ToolUtil.isNotEmpty(platform)) {
        		rfffParam.setPlatform(platform);
            }
            
            String  outWNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(13));//出仓单号
            if (ToolUtil.isNotEmpty(outWNumber)) {
        		rfffParam.setOutWNumber(outWNumber);
            }
            
            String  inOrderNumber = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(14));//内部订单号
            if (ToolUtil.isNotEmpty(inOrderNumber)) {
        		rfffParam.setInOrderNumber(inOrderNumber);
            }
            
            String  outTime = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(15));//出库日期
            if (ToolUtil.isNotEmpty(outTime)) {
        		rfffParam.setOutTime(outTime);
            }
            
            String  states = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(16));//状态
            if (ToolUtil.isNotEmpty(states)) {
        		rfffParam.setStates(states);
            }
            
            String  expressName = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(17));//快递公司
            if (ToolUtil.isNotEmpty(expressName)) {
        		rfffParam.setExpressName(expressName);
            }
            
            String  provinces = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(18));//省
            if (ToolUtil.isNotEmpty(provinces)) {
        		rfffParam.setProvinces(provinces);
            }
            
            String  city = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(19));//市
            if (ToolUtil.isNotEmpty(city)) {
        		rfffParam.setCity(city);
    			if(city.equals("深圳市")||city.equals("深圳")) {
    				rfffParam.setRemarks("深圳");
    			}
            }
            
            String  area = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(20));//区
            if (ToolUtil.isNotEmpty(area)) {
        		rfffParam.setArea(area);
    			if(area.equals("虎门镇")||area.equals("虎门港管委会")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            String  street = cn.stylefeng.guns.core.util.DateUtil.getCellValueByCell(row.getCell(21));//街道
            if (ToolUtil.isNotEmpty(street)) {
        		rfffParam.setStreet(street);
    			if(street.equals("虎门镇")) {
    				rfffParam.setRemarks("虎门");
    			}
            }
            
            if(ToolUtil.isNotEmpty(rfffParam.getChargingWeightKg())) {
        		//匹配软风报价   计算快递费，邮政账单收入、利润
            	BigDecimal chargingWeightKg = new BigDecimal(rfffParam.getChargingWeightKg());//计费重量
            	if(ToolUtil.isNotEmpty(rfffParam.getProvince())) {
            		BigDecimal sumExpressMoney = new BigDecimal("0");//总快递费
            		String provincestr = "";
            		if(ToolUtil.isNotEmpty(rfffParam.getRemarks())) {
            			provincestr = rfffParam.getRemarks();
            		} else {
            			provincestr = rfffParam.getProvince();
            		}
            		for (ToolExpressReconciliationYfInstallResult i : rfi) {
						if(provincestr.equals(i.getDestination())) {
							if(provincestr.equals("海南")||provincestr.equals("新疆")||provincestr.equals("西藏")) {
								if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
									BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
									BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
									BigDecimal wmulcCWeight = chargingWeightKg.multiply(cCWeight);//重量*续重
									BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
									sumExpressMoney = expressMoney;
									rfffParam.setUnilateralism(unilateralism.toString());
									rfffParam.setCWeight(cCWeight.toString());
									rfffParam.setExpressMoney(expressMoney.toString());//快递费
								}
							} else {
            					if(chargingWeightKg.compareTo(as) == 1 && chargingWeightKg.compareTo(yi) != 1) {//0 - 1kg
            						if(ToolUtil.isNotEmpty(i.getQj01kg())) {
    									rfffParam.setExpressMoney(i.getQj01kg());//快递费
    									BigDecimal qj01kg = new BigDecimal(i.getQj01kg());
    									sumExpressMoney = qj01kg;
            						}
            					}
            					if(chargingWeightKg.compareTo(yi) == 1 && chargingWeightKg.compareTo(er) != 1) {//1 - 2kg
            						if(ToolUtil.isNotEmpty(i.getQj12kg())) {
    									rfffParam.setExpressMoney(i.getQj12kg());//快递费
    									BigDecimal qj12kg = new BigDecimal(i.getQj12kg());
    									sumExpressMoney = qj12kg;
            						}
            					}
            					if(chargingWeightKg.compareTo(er) == 1 && chargingWeightKg.compareTo(san) != 1) {//2 - 3kg
            						if(ToolUtil.isNotEmpty(i.getQj23kg())) {
    									rfffParam.setExpressMoney(i.getQj23kg());//快递费
    									BigDecimal qj23kg = new BigDecimal(i.getQj23kg());
    									sumExpressMoney = qj23kg;
            						}
            					}
            					if(chargingWeightKg.compareTo(san) == 1) {//3kg以上
    								if(ToolUtil.isNotEmpty(i.getUnilateralism()) && ToolUtil.isNotEmpty(i.getCWeight())) {
    									BigDecimal unilateralism = new BigDecimal(i.getUnilateralism());//面单费
    									BigDecimal cCWeight = new BigDecimal(i.getCWeight());//续重
    									BigDecimal wmulcCWeight = chargingWeightKg.multiply(cCWeight);//重量*续重
    									BigDecimal expressMoney = unilateralism.add(wmulcCWeight).setScale(3, BigDecimal.ROUND_HALF_UP);//面单费+（重量*续重）
    									sumExpressMoney = expressMoney;
    									rfffParam.setUnilateralism(unilateralism.toString());
    									rfffParam.setCWeight(cCWeight.toString());
    									rfffParam.setExpressMoney(expressMoney.toString());//快递费
    								}
            					}
							}
							if(ToolUtil.isNotEmpty(i.getAdditional())) {//地区加收
								BigDecimal additional = new BigDecimal(i.getAdditional());
								BigDecimal wmuladditional = chargingWeightKg.multiply(additional).setScale(4, BigDecimal.ROUND_HALF_UP);//重量*地区加收
								rfffParam.setAdditional(wmuladditional.toString());
								sumExpressMoney = sumExpressMoney.add(wmuladditional);
							}
							if(ToolUtil.isNotEmpty(i.getStandard()) && ToolUtil.isNotEmpty(i.getMinimum())) {//中转费
								rfffParam.setStandard(i.getStandard());
								rfffParam.setMinimum(i.getMinimum());
								BigDecimal standard = new BigDecimal(i.getStandard());//二三级中转标准
								BigDecimal minimum = new BigDecimal(i.getMinimum());//二三级中转最低
								BigDecimal wmulstandard = standard.multiply(chargingWeightKg).setScale(4, BigDecimal.ROUND_HALF_UP);//二三级中转标准*重量
								//如果  二三级中转标准*重量 > 二三级中转最低 就用二三级中转标准*重量 作为中转费，不然就用二三级中转最低作为中转费
								if(wmulstandard.compareTo(minimum) == 1) {
									rfffParam.setTransferMoney(wmulstandard.toString());
									sumExpressMoney = sumExpressMoney.add(wmulstandard);
								} else {
									rfffParam.setTransferMoney(i.getMinimum());
									sumExpressMoney = sumExpressMoney.add(minimum);
								}
							}
							if(chargingWeightKg.compareTo(wudwu) == 1) {//5.5kg以上
								if(ToolUtil.isNotEmpty(i.getZz55above())) {//5.5Kg以上的中转费
									BigDecimal zz55above = new BigDecimal(i.getZz55above());
									BigDecimal wmulzz55above = zz55above.multiply(chargingWeightKg).setScale(4, BigDecimal.ROUND_HALF_UP);
									rfffParam.setTransferMoney(wmulzz55above.toString());
									sumExpressMoney = sumExpressMoney.add(wmulzz55above);
								}
							}
							rfffParam.setSumExpressMoney(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//总快递费
							expressReconciliationYzParam.setIncome(sumExpressMoney.setScale(2, BigDecimal.ROUND_HALF_UP).toString());//收入
							break;
						}
					}
            	}
            }
            
            if(ToolUtil.isNotEmpty(expressReconciliationYzParam.getPostage()) && ToolUtil.isNotEmpty(rfffParam.getSumExpressMoney())) {
            	BigDecimal Postagebig = new BigDecimal(expressReconciliationYzParam.getPostage());//实收邮资
            	BigDecimal sumExpressMoney = new BigDecimal(rfffParam.getSumExpressMoney());//总快递费
            	BigDecimal profit = sumExpressMoney.subtract(Postagebig).setScale(2,BigDecimal.ROUND_HALF_UP);//总快递费  -  总邮资 = 利润
            	expressReconciliationYzParam.setProfit(profit.toString());//利润
            }
            
            yzlist.add(expressReconciliationYzParam);

            if(ToolUtil.isNotEmpty(customerName)) {
            	if(customerName.equals("软风2C")) {
            		rflist.add(rfffParam);
            	}
            }
        }
        if(yzlist.size() > 0) {
        	this.saveBatchList(yzlist);
        }
        if(rflist.size() > 0) {
        	toolExpressReconciliationRfService.saveBatchList(rflist);
        }
		return ResponseData.success();
	}
	
	@Override
	public boolean saveBatchList(List<ToolExpressReconciliationYzParam> list) {
		return this.baseMapper.saveBatchList(list);
	}

	@Override
	public void deleteBySpec(ToolExpressReconciliationYzParam param) {
		this.baseMapper.deleteBySpec(param);
	}

	@Override
	public Workbook exportToExcel(Long reconciliationId) {
	    //这是表头
	    String[] arr = {"序号","产品","邮件号","省份","云仓省份"," 1","寄达省名称","收寄时间","重量(克)","计费重量(千克)","取整重量","云仓重量(千克)","重量差异"
	    		,"总邮资","邮资核对","首重","续重","折扣","邮资差异","客户","收入","利润","重量区间","数量"};
	    //这是具体数据
	    List<List<String>> list = new ArrayList<>();
	    
	    ToolExpressReconciliationYzParam param = new ToolExpressReconciliationYzParam();
	    param.setReconciliationId(reconciliationId);
	    List<ToolExpressReconciliationYzResult> datalist = this.findListBySpec(param);
	    for (ToolExpressReconciliationYzResult r : datalist) {
		    List<String> list1 = new ArrayList<String>();
		    list1.add(r.getNoNumber());
		    list1.add(r.getProduct());
		    list1.add(r.getOrderNumber());
		    list1.add(r.getProvince());
		    list1.add(r.getYcProvince());
		    list1.add(r.getProvinceTrue());
		    list1.add(r.getProvinceName());
		    list1.add(r.getReceivingTime());
		    list1.add(r.getWeightK());
		    list1.add(r.getChargingWeightKg());
		    list1.add(r.getRoundingWeightKg());
		    list1.add(r.getYcWeightKg());
		    list1.add(r.getWeightDifference());
		    list1.add(r.getPostage());
		    list1.add(r.getPostageCheck());
		    list1.add(r.getFirstWeight());
		    list1.add(r.getWeightContinuation());
		    list1.add(r.getDiscount());
		    list1.add(r.getPostageDifference());
		    list1.add(r.getCustomerName());
		    list1.add(r.getIncome());
		    list1.add(r.getProfit());
		    list1.add(r.getWeightRange());
		    list1.add(r.getQuantity());
		    list.add(list1);
		}
	    return ExcelWrite.writeToExcelByList(arr, list);
	}

	@Override
	public List<orderCount> findorderCount(ToolExpressReconciliationYzParam param, Long operateId) {
		return this.baseMapper.findorderCount(param,operateId);
	}

}
